package com.zdzx.utils;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.zdzx.common.util.DateUtil;
import com.zdzx.common.util.DateUtil.DateFormat;




public class PoiUtil {
	

	/**
	 * 导出带图片
	 * @param data
	 * @param header
	 * @param sheetName
	 * @param response
	 * @param request
	 * @throws IOException
	 */
	@SuppressWarnings("resource")
	public static void exportByImg(List<String[]> data, String[] header, String sheetName, HttpServletResponse response,
			HttpServletRequest request) throws IOException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet(sheetName == null ? "data" : sheetName);
		sheet.setColumnWidth(0, 1000);
		sheet.setColumnWidth(1, 3000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 8000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		HSSFRow headerRow = sheet.createRow(0);
		if (header != null) {

			for (int i = 0; i < header.length; i++) {
				headerRow.createCell(i).setCellValue(header[i]);
				headerRow.setHeightInPoints(20);
			}
		}
		if(data!=null&&data.size()>0) {
		for (int i = 0; i < data.size(); i++) {
			int j = 0; 
			HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
			dataRow.setHeightInPoints(15);
			for (;j < data.get(0).length; j++) {
				if(j>=header.length){
					if(data.get(i)[j]!=null){
					ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
					BufferedImage bufferImg = ImageIO.read(new URL(data.get(i)[j]));  
					ImageIO.write(bufferImg, "PNG", byteArrayOut);
					HSSFClientAnchor anchor = new HSSFClientAnchor(j, j, 255, 255,(short) j, sheet.getLastRowNum(), (short) j, sheet.getLastRowNum() );
					HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
					patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
					}
			}else {
				try {
					if(data.get(i)[j] != null ) {
					Double parseDouble = Double.parseDouble(data.get(i)[j]);
					BigDecimal big=new BigDecimal(data.get(i)[j]);
					parseDouble=big.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
					dataRow.createCell(j).setCellValue(parseDouble!=null?parseDouble:0.00);
					}else {
						dataRow.createCell(j).setCellValue(data.get(i)[j] != null ? data.get(i)[j] : "");
					}
				}catch (NumberFormatException e) {
					dataRow.createCell(j).setCellValue(data.get(i)[j] != null ? data.get(i)[j] : "");
				}
			}
			}
		}
		}
	//	String fileName = (sheetName == null ? "data" : sheetName) + ".xls";
		// String mimeType = request.getServletContext().getMimeType(fileName);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
		response.setHeader("content-disposition",
				"attchment;filename=" + DateUtil.getCurrentTime(DateFormat.YYYY_MM_DD) + ".xls");
		workBook.write(response.getOutputStream());
	}
	
	
	/**
	 * 导出
	 * @param data
	 * @param header
	 * @param sheetName
	 * @param response
	 * @param request
	 * @throws IOException
	 */
	@SuppressWarnings("resource")
	public static void export(List<String[]> data, String[] header, String sheetName, HttpServletResponse response,
			HttpServletRequest request) throws IOException {
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet = workBook.createSheet(sheetName == null ? "data" : sheetName);
		sheet.setColumnWidth(0, 4000);
		sheet.setColumnWidth(1, 3000);
		sheet.setColumnWidth(2, 3000);
		sheet.setColumnWidth(3, 15000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 7000);
		XSSFRow headerRow = sheet.createRow(0);
		if (header != null) {

			for (int i = 0; i < header.length; i++) {
				XSSFCellStyle createCellStyle = workBook.createCellStyle();
				createCellStyle.setAlignment(HorizontalAlignment.CENTER);
				createCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
				//headerRow.setRowStyle(createCellStyle);
				
				XSSFCell createCell = headerRow.createCell(i);
				createCell.setCellStyle(createCellStyle);
				createCell.setCellValue(header[i]);
				headerRow.setHeightInPoints(30);
				
			}
		}
		if(data!=null&&data.size()>0) {
		for (int i = 0; i < data.size(); i++) {
			XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
			dataRow.setHeightInPoints(15);
			for (int j = 0; j < data.get(i).length; j++) {
				dataRow.createCell(j).setCellValue(data.get(i)[j] != null ? data.get(i)[j] : "");

			}
		}
		}
	//	String fileName = (sheetName == null ? "data" : sheetName) + ".xls";
		// String mimeType = request.getServletContext().getMimeType(fileName);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
		response.setHeader("content-disposition",
				"attchment;filename=" + DateUtil.getCurrentTime(DateFormat.YYYY_MM_DD) + ".xls");
		workBook.write(response.getOutputStream());
	}
	
	
	/**
	 * 导出
	 * @param data
	 * @param header
	 * @param sheetName
	 * @param response
	 * @param request
	 * @throws IOException
	 */
	@SuppressWarnings("resource")
	public static void exportList(Map<String,List<String[]>> data, String[] header, String[] sheetNames, HttpServletResponse response,
			HttpServletRequest request) throws IOException {
		XSSFWorkbook workBook = new XSSFWorkbook();
		for(String sheetName : sheetNames){
			XSSFSheet sheet = workBook.createSheet(sheetName == null ? "data" : sheetName);
			sheet.setColumnWidth(0, 1000);
			sheet.setColumnWidth(1, 3000);
			sheet.setColumnWidth(2, 5000);
			sheet.setColumnWidth(3, 8000);
			sheet.setColumnWidth(4, 5000);
			sheet.setColumnWidth(5, 5000);
			sheet.setColumnWidth(6, 5000);
			for (int i=0;i<sheetNames.length;i++) {
				List<String[]> tempData = data.get(sheetName);
				System.out.println(sheetName);
				System.out.println(i);
				XSSFRow headerRow = sheet.createRow(0);
				if (header != null) {

					for (int j = 0; j < header.length; j++) {
						headerRow.createCell(j).setCellValue(header[j]);
						headerRow.setHeightInPoints(50);
					}
				}
				if(tempData!=null&&tempData.size()>0) {
					
				for (int k = 0; k < tempData.size(); k++) {
					XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
					dataRow.setHeightInPoints(15);
					for (int l = 0; l < tempData.get(k).length; l++) {
						dataRow.createCell(l).setCellValue(tempData.get(k)[l] != null ? tempData.get(k)[l] : "");

					}
				}
				}	
			}
			
		}
		
		
		
	//	String fileName = "data.xls";
		// String mimeType = request.getServletContext().getMimeType(fileName);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
		response.setHeader("content-disposition",
				"attchment;filename=" + DateUtil.getCurrentTime(DateFormat.YYYY_MM_DD) + ".xls");
		workBook.write(response.getOutputStream());
	}
	
	
	/**
	 * 导入（日期临时）
	 * 
	 * @param input
	 * @param flag
	 *            是否带表头 Y,N
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings("resource")
	public static Map<String, List<String>> NewImportXLS(InputStream input, String flag) throws IOException {
		Map<String, List<String>> page = new HashMap<String, List<String>>();
		XSSFWorkbook workBook = new XSSFWorkbook(input);
		for (Sheet sheet : workBook) {
			XSSFSheet sheetAt = workBook.getSheet(sheet.getSheetName());
			List<String> list = new ArrayList<String>();
			for (Row row : sheetAt) {
				int rowNum = row.getRowNum();
				if ("Y".equals(flag) && rowNum == 0) {
					continue;
				}
				short lastCellNum = row.getLastCellNum();
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < lastCellNum; i++) {
					if (null != row.getCell(i)) {
						Object value = null;
						CellType cellType = row.getCell(i).getCellType();
						if (i == 7) {
							Date dateCellValue = row.getCell(i).getDateCellValue();
							SimpleDateFormat format = new SimpleDateFormat("yyy/MM/dd");
							value = format.format(dateCellValue);
						} else if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
							value = row.getCell(i).getNumericCellValue();
						} else {
							value = (null == row.getCell(i).getStringCellValue() ? ""
									: row.getCell(i).getStringCellValue());
						}

						buffer.append(value).append("===");
					}
				}
				list.add(buffer.toString().trim());
			}
			page.put(sheet.getSheetName(), list);
		}
		return page;
	}
	
	
	/**
	 * 导入
	 * 
	 * @param input
	 * @param flag
	 *            是否带表头 Y,N
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings("resource")
	public static Map<String, List<String>> ImportXLS(InputStream input, String flag) throws IOException {
		Map<String, List<String>> page = new HashMap<String, List<String>>();
		XSSFWorkbook workBook = new XSSFWorkbook(input);
		for (Sheet sheet : workBook) {
			XSSFSheet sheetAt = workBook.getSheet(sheet.getSheetName());
			List<String> list = new ArrayList<String>();
			for (Row row : sheetAt) {
				int rowNum = row.getRowNum();
				if ("Y".equals(flag) && rowNum == 0) {
					continue;
				}
				short lastCellNum = row.getLastCellNum();
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < lastCellNum; i++) {
					if (null != row.getCell(i)) {
						Object value = null;
						CellType cellType = row.getCell(i).getCellType();
						if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
							value = row.getCell(i).getNumericCellValue();
						} else {
							value = (null == row.getCell(i).getStringCellValue() ? ""
									: row.getCell(i).getStringCellValue());
						}
						if(i>0)
						buffer.append("zzzzz").append(value);
						else
						buffer.append(value);	
					}
				}
				list.add(buffer.toString().trim());
			}
			page.put(sheet.getSheetName(), list);
		}
		return page;
	}
}
